require 'rubygems'
require 'parseexcel'

class StudentExcel
  # represents an excel spreadsheet of students
  attr_reader :school, :class, :coach, :students
  def initialize(file, 
                 group_school = {:column => 'c', :row => 2},
                 group_school_regex = Regexp.new(/School:\s+(.*)/),
                 group_class = {:column => 'c', :row => 3},
                 group_class_regex = Regexp.new(/Class:\s+(.*)/),
                 group_coach = {:column => 'c', :row => 4},
                 group_coach_regex = Regexp.new(/Coach\'s Name:\s+(.*)/),
                 student_headers = 7,
                 first_student_row = 9)
    @wb = Spreadsheet::ParseExcel.parse(file)
    @ws = @wb.worksheet(0)
    @first_student_row, @student_headers = first_student_row, student_headers
    @group_school_regex, @group_class_regex, @group_coach_regex = group_school_regex, group_class_regex, group_coach_regex
    
    @school = @group_school_regex.match(get_cell_contents(group_school[:row], group_school[:column]))[1]
    @class = @group_class_regex.match(get_cell_contents(group_class[:row], group_class[:column]))[1]
    @coach = @group_coach_regex.match(get_cell_contents(group_coach[:row], group_coach[:column]))[1]

    @students = get_students()
  end

  def get_students()
    students = []
    header_row = @ws.row(@student_headers)
    begin 
      @ws.each(translate_row(@first_student_row)) do |row|
        s = {}
        # I'd prefer this be some sophisticated map dealy that makes it easy to override,
        # but its late, I'm watching the Bears and I just don't care right now. ;) CTM
        next if row.at(1).to_s('latin1') == '' # skip to the next one unless this row has info
        s['last_name'] = row.at(1).to_s('latin1')
        s['first_name'] = row.at(2).to_s('latin1')
        s['gender'] = row.at(3).to_s('latin1')
        s['dob'] = row.at(4).to_s('latin1')
        s['grade'] = row.at(5).to_i
        s['shoe_size'] = row.at(6).to_i
        s['t_shirt_size'] = row.at(7).to_s('latin1')
        s['goal'] = row.at(8).to_i
        students << s
      end
    rescue
      puts "error on student #{s['first_name']}" 
    end
    return students
  end

  def get_cell_contents(row, column)
    # returns a cell in the spreadsheet
    column = translate_column(column)
    row = translate_row(row)
    return @ws.row(row).at(column).to_s('latin1') 
  end

  def translate_column(column)
    # returns the parseexcel version of the column id a => 0, b => 1 etc
    columns = %w[a b c d e f g h i]
    return columns.index(column)
  end

  def translate_row(row)
    # returns the parseexcel version of the row, row - 1
    return row - 1
  end
    
end



    
